﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace book_management_system
{
    public partial class Books : Form
    {
        public Books()
        {
            InitializeComponent();
            populate();//初始化popu窗体
        }

        SqlConnection Con = new SqlConnection(@"Data Source=Kami;Initial Catalog=WhiteBookShopDb;Integrated Security=True");//连接或打开
        private void populate()
        {
            Con.Open();//打开数据库
            string query = "select * from BookTb1";
            SqlDataAdapter sda = new SqlDataAdapter(query,Con);
            SqlCommandBuilder builder = new SqlCommandBuilder(sda);//SqlDataAdapter常与SqlCommandBuilder配合使用进行批量操作
            var ds = new DataSet();
            sda.Fill(ds);
            BookDGV.DataSource = ds.Tables[0];
            Con.Close();//关闭数据库
        }
        private void Filter()//过滤
        {
            Con.Open();//打开数据库
            string query = "select * from BookTb1 where BCat = '"+CatCbSearchCb.SelectedItem.ToString()+"'";
            SqlDataAdapter sda = new SqlDataAdapter(query, Con);
            SqlCommandBuilder builder = new SqlCommandBuilder(sda);//SqlDataAdapter常与SqlCommandBuilder配合使用进行批量操作
            var ds = new DataSet();
            sda.Fill(ds);
            BookDGV.DataSource = ds.Tables[0];
            Con.Close();//关闭数据库
        }
        private void SaveBtn_Click(object sender, EventArgs e)
        {
            if(BTitleTb.Text == "" || BAutTb.Text == "" || QtyTb.Text == "" || PriceTb.Text == ""|| BCatCb.SelectedIndex == -1)
            {
                MessageBox.Show("信息缺失!!!");
            }
            else
            {
                try
                {
                    Con.Open();//打开数据库
                    string query = "insert into BookTb1 values('"+BTitleTb.Text+"','"+BAutTb.Text+ "','"+BCatCb.SelectedItem.ToString()+ "',"+QtyTb.Text+ ","+PriceTb.Text+")";
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("书籍信息保存成功!!");
                    Con.Close();//关闭数据库
                }
                catch(Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                }
                /* 刷新列表数据 */
                populate();//重新提取数据库数据
                Reset();//清空输入框的内容
            }
        }

        private void CatCbSearchCb_SelectionChangeCommitted(object sender, EventArgs e)
        {
            Filter();
        }

        private void button5_Click(object sender, EventArgs e)//刷新列表
        {
            populate();//重新提取数据库数据
        }

        private void Reset()
        {
            BTitleTb.Text = "";
            BAutTb.Text = "";
            BCatCb.SelectedIndex = -1;
            QtyTb.Text = "";
            PriceTb.Text = "";
        }
        private void ResetBtn_Click(object sender, EventArgs e)//重置按钮事件
        {
            Reset();//清空输入框的内容
        }

        int key = 0;
        private void BookDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            /* 点击列表的类型即可 */
            BTitleTb.Text = BookDGV.SelectedRows[0].Cells[1].Value.ToString();
            BAutTb.Text = BookDGV.SelectedRows[0].Cells[2].Value.ToString();
            BCatCb.SelectedItem = BookDGV.SelectedRows[0].Cells[3].Value.ToString();
            QtyTb.Text = BookDGV.SelectedRows[0].Cells[4].Value.ToString();
            PriceTb.Text = BookDGV.SelectedRows[0].Cells[5].Value.ToString();
            if(BTitleTb.Text == "")
            {
                key = 0;
            }
            else
            {
                key = Convert.ToInt32(BookDGV.SelectedRows[0].Cells[0].Value.ToString());
            }
        }

        private void DeleteBtn_Click(object sender, EventArgs e)//删除数据条
        {
            if (key == 0)
            {
                MessageBox.Show("信息缺失!!!");
            }
            else
            {
                try
                {
                    Con.Open();//打开数据库
                    string query = "delete from BookTb1 where BId = "+key+"";
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("书籍信息删除完成!!");
                    Con.Close();//关闭数据库
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                }
                /* 刷新列表数据 */
                populate();//重新提取数据库数据
                Reset();//清空输入框的内容
            }
        }

        private void EditBtn_Click(object sender, EventArgs e)//编辑数据条
        {
            if (BTitleTb.Text == "" || BAutTb.Text == "" || QtyTb.Text == "" || PriceTb.Text == "" || BCatCb.SelectedIndex == -1)
            {
                MessageBox.Show("信息缺失!!!");
            }
            else
            {
                try
                {
                    Con.Open();//打开数据库
                    string query = "update BookTb1 set BTitle='"+BTitleTb.Text+"',BAuthor='"+BAutTb.Text+"',BCat='"+BCatCb.SelectedItem.ToString()+"',BQty="+QtyTb.Text+",BPrice="+PriceTb.Text+"where BId="+key+"";
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("书籍信息更新完成!!");
                    Con.Close();//关闭数据库
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                }
                /* 刷新列表数据 */
                populate();//重新提取数据库数据
                Reset();//清空输入框的内容
            }
        }

        private void ExitBtn_Click(object sender, EventArgs e)//退出按钮事件
        {
            Application.Exit();
        }

        private void Backlab_Click(object sender, EventArgs e)
        {
            Login obj = new Login();
            obj.Show();
            this.Hide();
        }

        private void Userslab_Click(object sender, EventArgs e)
        {
            Users obj = new Users();
            obj.Show();
            this.Hide();
        }

        private void Dashlab_Click(object sender, EventArgs e)
        {
            DashBoard obj = new DashBoard();
            obj.Show();
            this.Hide();
        }
    }
}
